﻿using System;
using System.Collections.Generic;
using System.ComponentModel;
using System.Data;
using System.Drawing;
using System.Linq;
using System.Text;
using System.Windows.Forms;
using CrystalDecisions.CrystalReports.Engine;

namespace SchoolManagementSystem
{
    public partial class SPP : Form
    {
        private List<int> groupID = new List<int>();
        private List<string> stuID1 = new List<string>();
        private List<string> stuID2 = new List<string>();
        private SPPAdd sppadd = null;
        private SPPEdit sppedit = null;
        private SPPDetail sppdetail = null;
        private BindingSource bindingSource = new BindingSource();
        private DataTable dtRiwayatPembayaran = null;

        public SPP()
        {
            InitializeComponent();
            Style.setDatagridviewStyle(dgvSPP);

            this.dgvSPP.CurrentCellChanged += new EventHandler(dgvSPP_CurrentCellChanged);
        }

        private void loadStudent()
        {
            string sql = "SELECT s.stu_id, stu_name FROM Student AS s, Enrollment AS e WHERE s.stu_id=e.stu_id AND group_id=" + groupID[cmbGrup.SelectedIndex] + " AND enr_session='" + (num1.Value + " - " + num2.Value) + "' ORDER BY stu_name";
            DataTable dt = Database.getInstance().query(sql);

            // Add IDs and names
            stuID1.Clear();
            lstMurid.Items.Clear();
            foreach (DataRow row in dt.Rows)
            {
                stuID1.Add(row[0].ToString());
                lstMurid.Items.Add(row[1].ToString());
            }
        }

        private void num2_ValueChanged(object sender, EventArgs e)
        {
            if (cmbGrup.SelectedIndex < 0) return;
            num1.Value = num2.Value - 1;
            loadStudent();
        }

        private void num1_ValueChanged(object sender, EventArgs e)
        {
            if (cmbGrup.SelectedIndex < 0) return;
            num2.Value = num1.Value + 1;
            loadStudent();
        }

        private void SPP_Load(object sender, EventArgs e)
        {
            DataTable dt = Database.getInstance().getGroups();

            // Load group
            foreach (DataRow row in dt.Rows)
            {
                groupID.Add(int.Parse(row[0].ToString()));
                cmbGrup.Items.Add(row[1].ToString());
            }

            // Load School Session
            DateTime date = DateTime.Now;
            if (date.Month < 7)
            {
                num1.Value = date.Year - 1;
                num2.Value = num1.Value + 1;
            }
        }

        private void button1_Click(object sender, EventArgs e)
        {
            if (txtInduk.Text.Length == 0)
            {
                MessageBox.Show("Mohon pilih salah satu murid terlebih dahulu", "Peringatan", MessageBoxButtons.OK, MessageBoxIcon.Exclamation);
                return;
            }

            if (this.sppadd == null || this.sppadd.IsDisposed)
                sppadd = new SPPAdd(this.txtInduk.Text, this.txtNama.Text);

            //check whether the form has been opened or not
            if (((Main)this.MdiParent).isOpenForm(sppadd))
            {
                sppadd = null;
                return;
            }

            sppadd.MdiParent = this.MdiParent;
            sppadd.Show();
            sppadd.Activate();
        }

        private void cmbGrup_SelectedIndexChanged(object sender, EventArgs e)
        {
            if (cmbGrup.SelectedIndex >= 0) 
                loadStudent();
        }

        private void btnCari_Click(object sender, EventArgs e)
        {
            if (txtCari.Text.Length == 0)
            {
                MessageBox.Show("Mohon isi nama murid yang akan dicari", "Peringatan", MessageBoxButtons.OK, MessageBoxIcon.Exclamation);
                return;
            }

            string sql = "SELECT stu_id, stu_name FROM Student WHERE stu_name LIKE '%" + txtCari.Text + "%'";
            DataTable dt = Database.getInstance().query(sql);

            // Add IDs and names
            stuID2.Clear();
            lstCari.Items.Clear();
            foreach (DataRow row in dt.Rows)
            {
                stuID2.Add(row[0].ToString());
                lstCari.Items.Add(row[1].ToString());
            }
        }

        private void lstMurid_SelectedIndexChanged(object sender, EventArgs e)
        {
            if (lstMurid.SelectedIndex == -1) return;

            refreshDataMurid(true);
            refreshRiwayatPembayaran(true);
        }

        private void refreshDataMurid(Boolean selectByClass)
        {
            string sql = null;

            if (selectByClass)
            {
                if (lstMurid.SelectedIndex == -1) return;
                sql = "SELECT s.stu_id, s.stu_name, s.stu_gender, p.par_name, s.stu_image FROM Student AS s LEFT JOIN Parent AS p ON s.par_id = p.par_id WHERE s.stu_id='" + this.stuID1[lstMurid.SelectedIndex] + "'";
            }
            else
            {
                if (lstCari.SelectedIndex == -1) return;
                sql = "SELECT s.stu_id, s.stu_name, s.stu_gender, p.par_name, s.stu_image FROM Student AS s LEFT JOIN Parent AS p ON s.par_id = p.par_id WHERE s.stu_id='" + this.stuID2[lstCari.SelectedIndex] + "'";
            }

            DataTable dTable = Database.getInstance().query(sql);
            foreach (DataRow row in dTable.Rows)
            {
                this.txtInduk.Text = row.ItemArray[0].ToString();
                this.txtNama.Text = row.ItemArray[1].ToString();

                if (row.ItemArray[2].ToString() == "L")
                {
                    this.txtKelamin.Text = "Laki - laki";
                }
                else if (row.ItemArray[2].ToString() == "P")
                {
                    this.txtKelamin.Text = "Perempuan";
                }

                this.txtOrtu.Text = row.ItemArray[3].ToString();

                if (row.ItemArray[4] != DBNull.Value)
                {
                    byte[] result = (byte[])row.ItemArray[4];
                    System.IO.MemoryStream ms = new System.IO.MemoryStream(result);
                    this.pctMurid.Image = Image.FromStream(ms);
                }
                else
                {
                    this.pctMurid.Image = null;
                }
            }
        }

        private void refreshRiwayatPembayaran(Boolean selectByClass)
        {
            string sql = null;

            this.dgvSPP.Columns.Clear();

            if (selectByClass)
            {
                sql = "SELECT * FROM SPP WHERE stu_id='" + this.stuID1[lstMurid.SelectedIndex] + "'";
            }
            else
            {
                sql = "SELECT * FROM SPP WHERE stu_id='" + this.stuID2[lstCari.SelectedIndex] + "'";
            }
           
            this.dtRiwayatPembayaran = Database.getInstance().query(sql);

            bindingSource.DataSource = this.dtRiwayatPembayaran;
            this.dgvSPP.DataSource = bindingSource;

            // Add Edit Column
            DataGridViewLinkColumn editColumn = new DataGridViewLinkColumn();
            editColumn.UseColumnTextForLinkValue = true;
            editColumn.Name = "Edit";
            editColumn.Text = "Edit";
            editColumn.HeaderText = "Edit";
            editColumn.ReadOnly = true;
            editColumn.VisitedLinkColor = Color.Blue;
            this.dgvSPP.Columns.Add(editColumn);
            this.dgvSPP.Columns["Edit"].Width = 50;
            this.dgvSPP.Columns["Edit"].DefaultCellStyle.Alignment = DataGridViewContentAlignment.MiddleCenter;

            // Add Delete Column
            DataGridViewLinkColumn deleteColumn = new DataGridViewLinkColumn();
            deleteColumn.UseColumnTextForLinkValue = true;
            deleteColumn.Name = "Delete";
            deleteColumn.HeaderText = "Delete";
            deleteColumn.Text = "Delete";
            deleteColumn.ReadOnly = true;
            deleteColumn.VisitedLinkColor = Color.Blue;
            this.dgvSPP.Columns.Add(deleteColumn);
            this.dgvSPP.Columns["Delete"].Width = 50;
            this.dgvSPP.Columns["Delete"].DefaultCellStyle.Alignment = DataGridViewContentAlignment.MiddleCenter;

            this.dgvSPP.Columns["id"].Width = 50;
            this.dgvSPP.Columns["year"].Width = 50;
            this.dgvSPP.Columns["month"].Width = 50;
            this.dgvSPP.Columns["stu_id"].Visible = false;

            this.dgvSPP.Columns["id"].HeaderText = "ID";
            this.dgvSPP.Columns["year"].HeaderText = "Tahun";
            this.dgvSPP.Columns["month"].HeaderText = "Bulan";
            this.dgvSPP.Columns["amount"].HeaderText = "Jumlah";
            this.dgvSPP.Columns["description"].HeaderText = "Keterangan";
            this.dgvSPP.Columns["remaining"].HeaderText = "Sisa hutang";

            this.dgvSPP.DefaultCellStyle.Alignment = DataGridViewContentAlignment.MiddleCenter;
        }

        private void btDetail_Click(object sender, EventArgs e)
        {
            if (this.dgvSPP.CurrentCell == null)
            {
                MessageBox.Show("Mohon pilih salah satu murid terlebih dahulu", "Peringatan", MessageBoxButtons.OK, MessageBoxIcon.Exclamation);
                return;
            }

            int rowIndex = this.dgvSPP.CurrentCell.RowIndex;

            string month = this.dgvSPP.Rows[rowIndex].Cells["month"].Value.ToString();
            string year = this.dgvSPP.Rows[rowIndex].Cells["year"].Value.ToString();
            string desc = this.dgvSPP.Rows[rowIndex].Cells["description"].Value.ToString();
            string status = null;

            if (0 == (decimal)this.dgvSPP.Rows[rowIndex].Cells["remaining"].Value)
            {
                status = "Lunas";
            }
            else
            {
                status = "Belum lunas";
            }
            
            if (this.sppdetail == null || this.sppdetail.IsDisposed)
                sppdetail = new SPPDetail(this.txtSPP.Text, this.txtInduk.Text, this.txtNama.Text, month, year, desc, status);

            //check whether the form has been opened or not
            if (((Main)this.MdiParent).isOpenForm(sppdetail))
            {
                sppdetail = null;
                return;
            }

            sppdetail.MdiParent = this.MdiParent;
            sppdetail.Show();
            sppdetail.Activate();
        }

        private void btnBayar_Click(object sender, EventArgs e)
        {
            if (checkPembayaranFieldValue() == true)
            {
                if (false == updateSPP(this.txtSPP.Text))
                {
                    return;
                }

                updateFinance();

                var result = MessageBox.Show("Tambah pembayaran berhasil !", "Sukses !", MessageBoxButtons.OK, MessageBoxIcon.Information);
                if (result == System.Windows.Forms.DialogResult.OK)
                {
                    var result2 = MessageBox.Show("Apakah transaksi ingin di-print ?", "Confirmasi !", MessageBoxButtons.YesNo, MessageBoxIcon.Question);
                    if (result2 == System.Windows.Forms.DialogResult.Yes)
                    {
                        printTransactionReport(this.txtSPP.Text);                        
                    }
                    else
                    {
                        this.Close();
                    }
                } 
            }
        }

        private void printTransactionReport(string spp_id)
        {
            DataTable dt = new DataTable();
            dt.Columns.Add("transaction_id");
            dt.Columns.Add("transaction_date");
            dt.Columns.Add("stu_name");
            dt.Columns.Add("stu_id");
            dt.Columns.Add("stu_class");
            dt.Columns.Add("transaction_desc");
            dt.Columns.Add("transaction_amount");
            dt.Columns.Add("transaction_detail");
            DataRow row = dt.NewRow();
           
            Cursor = Cursors.WaitCursor;

            string query = "SELECT * FROM SPP WHERE id='" + spp_id + "'";  
            DataTable dTable = Database.getInstance().query(query);
            if (dTable.Rows.Count == 1)
            {
                row["transaction_id"] = dTable.Rows[0]["id"];
                row["transaction_date"] = Utilities.getDateInIndonesia(this.dtpTanggal.Value);
                row["stu_name"] = this.txtNama.Text;
                row["stu_id"] = dTable.Rows[0]["stu_id"];
                row["stu_class"] = this.cmbGrup.SelectedItem;
                row["transaction_desc"] = this.txtKeterangan.Text;
                row["transaction_amount"] = Utilities.getNumberToWordInIndonesia(this.txtCicilan.Text);
            }

            dt.Rows.Add(row);
            
            
            ReportDocument rptDoc = new ReportDocument();
            alwardahDataSet ds = new alwardahDataSet();

            ds.Tables["Transaction"].Merge(dt);
            string appPath = (System.IO.Path.GetDirectoryName(Application.ExecutablePath));
            rptDoc.Load(appPath + "\\Report\\ReportTransaction.rpt");
            rptDoc.SetDataSource(ds);

            // siapkan report viewer
            ReportForm reportForm = new ReportForm(rptDoc);
            reportForm.ShowDialog();

            Cursor = Cursors.Default;
        }

        private void updateFinance()
        {
            DataTable dt = Database.getInstance().query("SELECT * FROM Finance");
            DataRow dataRow = dt.NewRow();
            dataRow["time"] = this.dtpTanggal.Value;
            if (this.diskon.Checked)
            {
                dataRow["type"] = "PENGELUARAN";
                dataRow["expense_id"] = 1;
            }
            else
            {
                dataRow["type"] = "PEMASUKAN";
                dataRow["income_id"] = 1;
            }
            dataRow["description"] = this.txtKeterangan.Text;
            dataRow["amount"] = decimal.Parse(this.txtCicilan.Text);
            dataRow["acc_id"] = Database.getInstance().user_id;
            dataRow["spp_id"] = int.Parse(this.txtSPP.Text);
            dt.Rows.Add(dataRow);
            Database.getInstance().updateDB(dt);
        }

        private Boolean updateSPP(string spp_id)
        {
            string command = "SELECT * FROM SPP WHERE id='" + spp_id + "'";
            DataTable dt = Database.getInstance().query(command);
            if (dt.Rows.Count == 1)
            {
                decimal remaining = Decimal.Parse(dt.Rows[0]["remaining"].ToString());
                decimal cicilan = Decimal.Parse(this.txtCicilan.Text);

                if (remaining == 0)
                {
                    MessageBox.Show("Pembayaran sudah lunas. Tidak ada cicilan yang harus dibayar !", "Peringatan", MessageBoxButtons.OK, MessageBoxIcon.Exclamation);
                    return false;
                }

                if (cicilan > remaining)
                {
                    string message = String.Format("Jumlah pembayaran melebihi yang harus dibayar. Jumlah yang harus dibayar adalah {0}, jumlah cicilan yang dibayar adalah {1}", remaining, cicilan);
                    MessageBox.Show(message, "Peringatan", MessageBoxButtons.OK, MessageBoxIcon.Exclamation);
                    return false;
                }
                
                remaining = remaining - cicilan;

                if (remaining < 0)
                {
                    MessageBox.Show("Maaf proses pembayaran cicilan gagal", "Peringatan", MessageBoxButtons.OK, MessageBoxIcon.Exclamation);
                    return false;
                }

                dt.Rows[0]["remaining"] = remaining;

                Database.getInstance().update(dt);
            }

            return true;


        }

        private Boolean checkPembayaranFieldValue()
        {
            if (this.txtSPP.TextLength == 0)
            {
                MessageBox.Show("Pilih dahulu siswa !", "Peringatan!", MessageBoxButtons.OK, MessageBoxIcon.Exclamation);
                return false;
            }

            if (this.txtCicilan.TextLength == 0)
            {
                MessageBox.Show("Harap masukkan data besar cicilan !", "Peringatan!", MessageBoxButtons.OK, MessageBoxIcon.Exclamation);
                return false;
            }

            if (this.txtKeterangan.Text == "")
            {
                MessageBox.Show("Harap masukkan data keterangan !", "Peringatan!", MessageBoxButtons.OK, MessageBoxIcon.Exclamation);
                return false;
            }

            return true;
        }

        private void lstCari_SelectedIndexChanged(object sender, EventArgs e)
        {
            if (lstCari.SelectedIndex == -1) return;

            refreshDataMurid(false);
            refreshRiwayatPembayaran(false);
        }

        private void pctMurid_Click(object sender, EventArgs e)
        {

        }

        private void dgvSPP_CellContentClick(object sender, DataGridViewCellEventArgs e)
        {
            if (this.dgvSPP.Columns[e.ColumnIndex].Name == "Edit")
            {
                editData(e.RowIndex);
            }
            else if (this.dgvSPP.Columns[e.ColumnIndex].Name == "Delete")
            {
                deleteData(e.RowIndex);
            }
        }

        private void deleteData(int rowIndex)
        {
            var result = MessageBox.Show("Apakah Anda ingin menghapus data tersebut ?", "Peringatan !", MessageBoxButtons.OKCancel, MessageBoxIcon.Exclamation);
            if (result == System.Windows.Forms.DialogResult.OK)
            {
                this.dgvSPP.Rows.RemoveAt(rowIndex);
                this.dtRiwayatPembayaran.Rows[rowIndex].Delete();
                Database.getInstance().updateDB(this.dtRiwayatPembayaran);
                this.dgvSPP.Refresh();
            }
        }

        private void editData(int rowIndex)
        {
            if (this.sppedit == null || this.sppedit.IsDisposed)
                sppedit = new SPPEdit(this, this.dtRiwayatPembayaran, rowIndex,
                    this.txtSPP.Text,
                    this.txtInduk.Text,
                    this.txtNama.Text,
                    this.dgvSPP.Rows[rowIndex].Cells["year"].Value,
                    this.dgvSPP.Rows[rowIndex].Cells["month"].Value,
                    this.dgvSPP.Rows[rowIndex].Cells["description"].Value.ToString(),
                    this.dgvSPP.Rows[rowIndex].Cells["amount"].Value);

            this.sppedit.MdiParent = this.MdiParent;
            this.sppedit.Show();
            this.sppedit.Activate();
        }

        private void dgvSPP_CurrentCellChanged(object sender, EventArgs e)
        {
            if (this.dgvSPP.CurrentCell == null) return;

            int rowIndex = this.dgvSPP.CurrentCell.RowIndex;

            this.txtSPP.Text= this.dgvSPP.Rows[rowIndex].Cells["id"].Value.ToString();
        }

        private void txtCicilan_TextChanged(object sender, EventArgs e)
        {
            TextBox txtInput = (TextBox)sender;

            if (txtInput.TextLength != 0)
            {
                for (int i = 0; i < txtInput.TextLength; i++)
                {
                    if (Char.IsDigit(txtInput.Text, i))
                    {
                        // Do not do anything
                    }
                    else
                    {
                        MessageBox.Show("Harap gunakan angka !", "Peringatan!", MessageBoxButtons.OK, MessageBoxIcon.Exclamation);
                        break;
                    }
                }
            }    
        }

        public void refreshTableData()
        {
            // refreshRiwayatPembayaran();
        }

        private void groupBox2_Enter(object sender, EventArgs e)
        {

        }
    }
}
